package com.browsesoft.oa;

import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import com.aote.model.DataShowTableModel;
import com.aote.util.StringHelper;
import com.browsesoft.dbtools.DBTools;
import com.browsesoft.htmlcomponent.HTMLBasicComponent;
import com.browsesoft.htmlcomponent.HTMLBasicModel;
import com.browsesoft.htmlcomponent.HTMLFormModel;
import com.browsesoft.htmlcomponent.ReplacableParameterComponent;
import com.browsesoft.util.RequestHelper;

/**
 * 执行sql的table模型 1 需要执行的sql语句定义在页面中
 */

public class HTMLSqlQueryModel extends DataShowTableModel {
	private List executesqls;

	/**
	 * 是否每次装载
	 */
	private String loadEvery = "";

	/**
	 * 页面中定义的sql
	 */
	protected List sqlElements = new LinkedList();

	public void init(HttpServletRequest request) throws Exception {
		super.init(request);
		// 得到页面中定义的sql
		this.sqlElements = getSqlConfig(this.component.getElement());
		this.loadEvery = this.component.getElement().getAttribute("loadevery");
		// 初始化数据处理
		initDatasProcess(request);
	}

	/**
	 * 初始化数据处理
	 */
	private void initDatasProcess(HttpServletRequest request) throws Exception {
		String className = this.component.getElement().getAttribute(
				"initprocessor");
		if (className != null && !className.equals("")) {
//			InitDatasProcessor idp = (InitDatasProcessor) Class.forName(
//					className).newInstance();
//			idp.process(request, this.component, this.getDatas(request));
		}
	}

	protected void setDatas(HttpServletRequest request) {
		String action = request.getParameter("action");
		// 如果是连续数据录入中的查询，清除数据
		if (action != null && action.equals("next")) {
			this.datas.clear();
		} else if (action != null || !this.loadEvery.equals("")) {
			this.datas = this.getDatas(request);
		}
	}

	/**
	 * 得到所有数据
	 */
	private List getDatas(HttpServletRequest request) {
		// 用请求中的参数替换sql中的 "#xxx#" 同名参数
		executesqls = getSqls(request, this.sqlElements, this.component);
		return getQueryDatas(executesqls);
	}

	/**
	 * 得到执行时的sql语句,添加了limit分页处理,所以只能用于mysql
	 * 
	 * @param request
	 * @return
	 */
	public static List getSqls(HttpServletRequest request, List list,
			HTMLBasicComponent component) {
		try {
			List result = new LinkedList();
			Iterator iter = list.iterator();
			while (iter.hasNext()) {
				Element element = (Element) iter.next();
				Element sqlConfigElem = (Element) element.cloneNode(true);
				// 取得select,where,order by ,
				String select = sqlConfigElem.getAttribute("value");
				// 替换select里面的参数
				select = replaceParams(request, component, select);
				// 如果有wherecondition条件
				if (sqlConfigElem.getElementsByTagName("wherecondition")
						.getLength() > 0) {
					Element whereElem = (Element) sqlConfigElem
							.getElementsByTagName("wherecondition").item(0);
					String connMark = whereElem.getAttribute("connmark");
					String where = getWhereStr(whereElem, request, connMark,
							component);
					String otherCondition = getOtherCondition(sqlConfigElem, request, component);
					if (where != null && !where.equals("")) {
						select = select + " where " + where;
					}
					select = select + " " + otherCondition;
				}
				System.out.println(select);
				result.add(select);
			}
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 替换sql语句中的参数
	 */
	private static String replaceParams(HttpServletRequest request,
			HTMLBasicComponent component, String select) {
		try {
			HTMLBasicModel model = component.getParent().getModel();
			if (model instanceof HTMLFormModel) {
				HTMLFormModel formModel = (HTMLFormModel) model;
				Map map = new HashMap(formModel.getAttributes());
				select = RequestHelper.replaceParameter(select, map, false);
			}
			select = RequestHelper.replaceParameter(request, select);
			return select;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	private static String getOtherCondition(Element sqlConfigElem,
			HttpServletRequest request, HTMLBasicComponent component) {
		if (sqlConfigElem.getElementsByTagName("othercondition").getLength() > 0) {
			Element elem = (Element) sqlConfigElem.getElementsByTagName(
					"othercondition").item(0);
			String result = elem.getAttribute("value");
			// 替换其它条件中的参数
			result = replaceParams(request, component, result);
			return result;
		}
		return "";
	}

	/**
	 * 得到where条件
	 */
	private static String getWhereStr(Element whereElem,
			HttpServletRequest request, String connMark,
			HTMLBasicComponent component) {
		String result = "";
		NodeList conditions = whereElem.getElementsByTagName("condition");
		for (int i = 0; i < conditions.getLength(); i++) {
			Element condition = (Element) conditions.item(i);
			String field = condition.getAttribute("field");
			String mark = condition.getAttribute("connmark");
			String fieldValue = condition.getAttribute("value");
			// 如果配置了请求参数名,且请求中没有该参数,则不添加这个条件
			String param = condition.getAttribute("param");
			if (!StringHelper.isNull(param)
					&& StringHelper.isNull(request.getParameter(param))) {
				continue;
			}
			fieldValue = RequestHelper.replaceParameter(request, fieldValue);
			if (fieldValue.indexOf("#") != -1
					&& component instanceof ReplacableParameterComponent) {
				fieldValue = ((ReplacableParameterComponent) component)
						.rplaceParameter(request, fieldValue, condition, "#");
			}
			if (!fieldValue.equals("") && fieldValue.indexOf("#") == -1
					&& !fieldValue.equals("''") && !fieldValue.equals("'%'")) {
				result += " " + field + mark + fieldValue + " " + connMark;
			}
		}
		if (result.endsWith(connMark)) {
			result = result.substring(0, result.length() - connMark.length());
		}
		return result;
	}

	/**
	 * 得到页面中定义的sql
	 */
	public static List getSqlConfig(Element element) {
		List result = new LinkedList();
		NodeList list = element.getElementsByTagName("sql");
		for (int i = 0; i < list.getLength(); i++) {
			Element elem = (Element) list.item(i);
			Element clone = (Element) elem.cloneNode(true);
			result.add(clone);
		}
		return result;
	}

	/**
	 * 执行多条sql得到数据
	 */
	private List getQueryDatas(List exectueSqls) {
		List result = new LinkedList();
		try {
			// 这里如果是第一条sql，直接转换成hashtable仿佛list，
			// 从第二条sql开始需要从list中取对应项得hashtable添加，所以使用for循环
			for (int i = 0; i < exectueSqls.size(); i++) {
				String sql = (String) exectueSqls.get(i);
				Logger.getLogger(this.getClass()).info(sql);
				String[][] temp = DBTools.executeQueryWithTableHead(sql);
				if (i == 0) {
					result.addAll(parseResult(temp));
				} else {
					updateDatas(result, temp);
				}
			}
		} catch (Exception e) {
			throw new RuntimeException("执行查询发生错误", e);
		}
		return result;
	}

	/**
	 * 转换结果
	 */
	public List parseResult(String[][] datas) {
		try {
			List result = new LinkedList();
			for (int i = 1; i < datas.length; i++) {
				Hashtable temp = DBTools.getAttributes(datas, i);
				result.add(temp);
			}
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 更新数据
	 */
	private void updateDatas(List datas, String[][] queryDatas) {
		try {
			for (int i = 1; i < queryDatas.length; i++) {
				Hashtable temp = DBTools.getAttributes(queryDatas, i);
				// 如果有该项hashtable
				if (datas.get(i - 1) != null) {
					Hashtable ht = (Hashtable) datas.get(i - 1);
					ht.putAll(temp);
				} else {
					datas.add(temp);
				}
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}
